<%-- 
    Document   : index
    Created on : 22 févr. 2012, 17:57:28
    Author     : Nargisse
--%>

<%@ page import="java.io.*, java.net.*, java.util.*" %>
<%@ page import="java.sql.*" %>
<%@ page import="org.w3c.dom.*, javax.xml.parsers.*" %>
<%@ page import="org.json.JSONObject" %>
<%@ page import="org.json.JSONArray" %>
<%@ page import="org.json.JSONException" %>
<%@ page import="sun.misc.BASE64Decoder" %>
<%@ page import="sun.misc.BASE64Encoder" %>
<%@ page import="javax.crypto.spec.SecretKeySpec" %>
<%@ page import="javax.crypto.Mac" %>
<%@ page import="java.io.UnsupportedEncodingException" %>
<%@ page import="java.security.InvalidKeyException" %>
<%@ page import="java.security.NoSuchAlgorithmException" %>


<% // scriplets
    System.out.println("###############");
    
    System.out.println("request");
     printRequestParams(request);
     
     System.out.println("signed_request");
     // Le parametre "signed_request" est utilise pour partager l'info entre l'application et facebook
     String sigreq = request.getParameter("signed_request");
     //Test avec cle fournie
    // String sigreq = "qmCgD3B4xeVE3lps2uG8YCdVJV7yTTkw5NfWhm1vKkE.eyJhbGdvcml0aG0iOiJITUFDLVNIQTI1NiIsImlzc3VlZF9hdCI6MTMzMDA4OTk1OCwidXNlciI6eyJjb3VudHJ5IjoiZnIiLCJsb2NhbGUiOiJmcl9GUiIsImFnZSI6eyJtaW4iOjIxfX19";
    if (sigreq == null) {
        out.write(errMessage);
        return;
    }
    //connection BDD
    connectToBDD();
       
     //System.out.println("parsing");

      JSONObject data = parseSignedRequest(sigreq, appSecret);
            if (data != null) {
                 //System.out.println("part1 - data: " + data.toString());
		
		// on entre dans ce if qu'une fois que le user a autorise notre application
		// car c'est seulement a ce moment qu'on obtient le oauth_token et le user_id
                if (data.has("oauth_token") && data.has("user_id")) {
		 //recuperer l'userID et le token contenu dans l'objet JSON
                    String fb_id = data.getString("user_id");
                    String oauth_token = data.getString("oauth_token");
                    String ipAddress = request.getRemoteAddr();

                   /* if (!checkUserPermissions(oauth_token)) {
                        //si l'utilisateur n'a pas autorisé notre app
                        authorize(out);
                        System.out.println("OUT => "+out);
                        return;
                    }*/
                    
                    System.out.println("TEST1");
                    if (!login(oauth_token, fb_id, ipAddress)) {
                        System.out.println("PB!!!!!!");
                        out.write(errMessage);
                        out.write(loginError);
                        return;
                    }
                    //Deconnection BDD
                    deconnectToBDD();
                    
                   // System.out.println("TEST2");
                    //construction du fichier "fb_jsp.log"
                    //logger.println("Login success: " + fb_id);
                    //logger.println("Session key: " + sessionKey);
                   // logger.flush();
                   // System.out.println("Login success: " + fb_id);
                    //System.out.println("Session key: " + sessionKey);
                    comp3++;
                    
                } else {
                    // si le user n'a pas encore autorise notre application on entre dans le else ??
                     comp2++;
                    authorize(out);
                    return;
                }
            } else {
                comp++;
                out.write(errMessage);
                return;
            }
     
     

%>

<%!
    public static Connection connectToBDD;
    public static Statement st;
    public static  Statement myStatement;
    public int comp = 0;
    public int comp2 = 0;
    public int comp3 = 0;
    public final String errMessage = "Server error! Sorry.";
    public String appId = "243622595719459";
    public String appSecret = "bcb2e1e8e9ba4c0d976eb807548a66a4";
    public String appUrl = "http://apps.facebook.com/insa_power/";
    public String permissions[] = {"user_status","user_location", "friends_location", "user_likes", "friends_likes", "user_groups", "friends_groups",
          "user_events", "friends_events", "user_birthday", "friends_birthday", "user_education_history", "friends_education_history",
          "user_work_history","friends_work_history"};
    public String sessionKey = "";
    public String locale = "";
    public String name = "";
    public String sqlConnectionURL = "";
    public String sqlLogin = "";
    public String sqlPass = "";
    public String loginError = "";
    public String nameUser = "";
    public JSONObject jsonObject = new JSONObject();
    public JSONArray jsonArrayprofile = new JSONArray();
    public JSONArray jsonArrayFriends = new JSONArray();
    public JSONArray jsonArrayGroupes = new JSONArray();
    public JSONArray jsonArrayLikes = new JSONArray();
    public JSONArray jsonArrayEvents = new JSONArray();
    private static final String ALGORITHM_HMAC_SHA_256 = "HmacSHA256";
    
  

 
/**********************************************
** Fonction qui permet de se connecter       **
**              a la BDD                     **
**********************************************/        
public void connectToBDD() throws Exception{
    
    System.out.println("Connection BDD ");
    String url = "jdbc:mysql://178.170.114.48:3306/data_from_facebook";
    
    Class.forName("org.gjt.mm.mysql.Driver");
    connectToBDD = DriverManager.getConnection (url, "root",  ""); 
    
    if(connectToBDD == null){
        System.out.println("PB avec la connction a la BDD! ");
    }
    
   //Statement object is instantiated from connection object by calling createStatement() method. 
        //Statement object is to send and execute sql statements to database.
        /*  Statement has three method
        executeQuery() is used for SELECT statement.
        executeUpdate() is used for create, alter or drop table.
        execute() is used when sql statement written as string object.*/
} 

/**********************************************
** Fonction qui permet de se deconnecter     **
**              a la BDD                     **
**********************************************/  
 public void deconnectToBDD() throws SQLException{
     System.out.println("Deconnection BDD ");
     
     if(myStatement != null){
         myStatement.close();
     }
     
     if(connectToBDD != null){
         connectToBDD.close();
     }  
 }
    
    
/**********************************************
** Affiche les parametres et valeurs         **
** des requetes effectuees  		     **
** Pour construire le fichier "fb_jsp.log"   **
**********************************************/
public void printRequestParams(HttpServletRequest request) {
		  
        System.out.println("+++++++++++++");

        //System.out.println(request.getRequestURL().toString());

        //fonction qui enumere les noms des parametres demandes via la requete
        Enumeration paramNames = request.getParameterNames();
     //   System.out.println("-- "+paramNames.hasMoreElements());
        
	//while => parcorus de la liste des parametres utilises
	//hasMoreElements => teste si cette enumeration contient encore des elements
        while (paramNames.hasMoreElements()) {
            String paramName = (String) paramNames.nextElement();
				
           // System.out.println("paramName - " + paramName);

            //affiche les valeurs du parametre
            String[] paramValues = request.getParameterValues(paramName);
            if (paramValues.length == 1) {
                String paramValue = paramValues[0];
                if (paramValue.length() == 0) {
                    System.out.println("no value");
                    
                } 
                else{
                    System.out.println("***"+paramValue);
		   
                }
            }
            else{
                for (int i = 0; i < paramValues.length; i++){
                    System.out.println("###"+paramValues[i] );
                    
                }
            }
        }//FIN WHILE
       
    }

    /*************************************
** Fonction qui permet de recuperer **
** la signature HMAC SHA-256 et 	   **
** un objet JSON encode base64url   **
**************************************/
    public JSONObject parseSignedRequest(String signedRequest, String secret) {
       // System.out.println("Fct parseSignedRequest =>");

        String[] list = signedRequest.split("\\.");
        if (list.length >= 2) {
            String encodedSig = list[0];
            String payload = list[1];

            try {
		// L'object JSON contient les champs: user, algorithm, user_id, ...
                JSONObject data = new JSONObject(new String(fbDecodeBase64(payload)));
               // System.out.println("jsonobject data => "+data);
                
		 //on verifie si l'algo utilisee est bien celle utilisee par fb
                if (!"HMAC-SHA256".equalsIgnoreCase(data.getString("algorithm"))) {
                    System.out.println("Unknown algorithm. Expected HMAC-SHA256, signedRequest={}" + signedRequest);
                   
                    return null;
                }

		 //si la signature est correcte alors on renvoie les donnees contenues dans l'objet JSON
                if (verifySignedRequest(payload, encodedSig, secret)) {
                    System.out.println("Signed Requet verfied! signedRequest={}" + signedRequest);
                    
                    return data;

                } else {
                    System.out.println("Invalid Signed Requet! signedRequest={}"+signedRequest);
                }

            } catch (IOException e) {
                System.out.println("Error Decoding Base64, signedRequest="+signedRequest+" -- "+e);
               
            } catch (JSONException e) {

                System.out.println("Invalid Json Data, signedRequest="+signedRequest+" -- "+e); 
            }

        }
        return null;
    }

/********************************************
** Fonction qui verifie la signed_request	 **
********************************************/
    public static boolean verifySignedRequest(String payload, String encodedSig, String secret) {
        //System.out.println("Fct verifySignedRequest =>");
         try {
            //encode le string avec le charset UTF-8
            byte[] keyBytes = secret.getBytes("UTF-8");

            SecretKeySpec secretKeySpec = new SecretKeySpec(keyBytes, ALGORITHM_HMAC_SHA_256);
            Mac mac = Mac.getInstance(ALGORITHM_HMAC_SHA_256);
            mac.init(secretKeySpec);
            byte[] macBytes = mac.doFinal(payload.getBytes());

            String encodedMacBytes = fbEncodeBase64(macBytes);
            return encodedSig.equals(encodedMacBytes);

        } catch (NoSuchAlgorithmException e) {
            System.out.println("Unable to get HmacsSHA256 instance "+ e);
            
        } catch (InvalidKeyException e) {
            System.out.println("Invalid Key for HmacsSHA256  "+e);
            
        } catch (UnsupportedEncodingException e) {
            System.out.println("Error Getting Bytes Array, secret= "+secret+" -- "+e);
            
        }
        return false;
    }

/*************************************
** Fonction qui encode l'objet byte   **
** en base64url            	      **
**************************************/
    private static String fbEncodeBase64(byte[] bytes) {
       // System.out.println("Fct fbEncodeBase64 =>");

        String encodedMacBytes = new BASE64Encoder().encode(bytes);
        encodedMacBytes = encodedMacBytes.replaceAll("\\+", "-");
        encodedMacBytes = encodedMacBytes.replaceAll("/", "_");
        if (encodedMacBytes.endsWith("=")) {
            encodedMacBytes = encodedMacBytes.substring(0, encodedMacBytes.length() - 1);
        }
        return encodedMacBytes;
    }

/*************************************
** Fonction qui decode l'objet JSON	**
** qui est encode en base64url   	**
*************************************/
    private static byte[] fbDecodeBase64(String encoded) throws IOException {
       // System.out.println("Fct fbDecodeBase64 => ");

        String _encoded = encoded.replaceAll("-", "+");
        _encoded = _encoded.replaceAll("_", "/");
        return new BASE64Decoder().decodeBuffer(_encoded);
    }



/************************************************
** Focntion qui affiche l'URL d'autorisation  **
*************************************************/
    public void authorize(JspWriter out) {
        //System.out.println("Fct authorize => ");

        String url = getAuthorizeUrl();
        System.out.println("URL 1=> "+url);

        try {
            out.write("<script type='text/javascript'>top.location.href = '" + url + "';</script>");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }

/*******************************
** 	**
** 	**
*********************************/
    public boolean checkUserPermissions(String oauth_token) {
       // System.out.println("Fct checkUserPermissions => ");

        //use Old REST API to check user permissions
        try {
            String url;
            DocumentBuilderFactory dbf = DocumentBuilderFactory.newInstance();
            //Using this class(DocumentBuilder), an application programmer can obtain a Document from XML
            DocumentBuilder db = dbf.newDocumentBuilder();
            for (String str : permissions) {
                url = "https://api.facebook.com/method/users.hasapppermission?api_key=" + appId + "&ext_perm=" + str + "&access_token=" + oauth_token;
                InputStream stream = sendGETStream(url);
                Document doc = db.parse(stream);
                NodeList nlist = doc.getElementsByTagName("users_hasapppermission_response");
                //Si la reponse est 1 cela veut dire que l'user a autorise notre app
                Boolean value = Boolean.parseBoolean(nlist.item(0).getTextContent().equals("0") ? "false" : "true");
                //logger.println("ext_perm = " + str + " - " + value);
                if (!value) {
                    return false;
                }
            }
        } catch (Exception e) {
            //logger.println(e.getMessage());
            return false;
        }

        return true;
    }

/****************************************************************
** Fonction qui "prepare" l'url pour la demande d'autorisation **
** d'acces aux donnees de l'user											**
*****************************************************************/
    public String getAuthorizeUrl() {
       // System.out.println("Fct getAuthorizeUrl => ");
   //idem? https://www.facebook.com/dialog/oauth?client_id=YOUR_APP_ID&redirect_uri=YOUR_CANVAS_PAGE

        String result = "https://graph.facebook.com/oauth/authorize?client_id=" + appId + "&redirect_uri=" + appUrl;

/*you must request specific permissions from the user.
This is accomplished by adding a scope parameter to the OAuth Dialog request followed by comma separated list of the required permissions*/
        if (permissions.length > 0) {
            result = result.concat("&scope=");
        }
		  //pour chaque permission qu'on desire demander a l'user via notre application, il faudra l'ajouter dans l'url de demande d'autorisation
		  //dans l'url chaque permission est separee d'une ","
        for (String str : permissions) {
            result = result.concat(str + ",");
        }
        return result;
        //return (permissions.length > 0) ? result.substring(0, result.length() - 1) : result;
    }
    
/*******************************
** **
** **
*********************************/
// USER
public void complete_user(long id_user, String nom_user, String prenom_user, String sexe_user, String anniv_user, long id_location, String update_date) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`user` (`id_user`, `nom`, `prenom`, `sexe`, `anniversaire`, `id_location`, `update_date`)"
        + "VALUES ('"+id_user+"', '"+nom_user+"', '"+prenom_user+"', '"+sexe_user+"', '"+anniv_user+"', '"+id_location+"', '"+update_date+"');");
}
public void complete_est_ami(long id_user, long id_friend) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`est_ami` (`id_user`, `id_friend`)"
        +"VALUES ('"+id_user+"', '"+id_friend+"');");
}
public void complete_location(long id_location, String nom_location) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`location` (`id_location`, `nom`)"
        + "VALUES ('"+id_location+"', '"+nom_location+"');");
}
//GEOLOCALISATION
public void complete_geolocalisation(long id_geo, String create_time_geo, long id_lieu, long id_user) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`geolocalisation` (`id_geo` ,`create_time`, `id_lieu`, `id_user_geo`)"
        +"VALUES ('"+id_geo+"', '"+create_time_geo+"', '"+id_lieu+"', '"+id_user+"');");    
} 
public void complete_lieu(long id_lieu, String nom_lieu, String rue_lieu, String ville_lieu, String pays_lieu, String zip_code_lieu, Double longitude_lieu, Double latitude_lieu) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`lieu` (`id_lieu` ,`nom`, `rue`, `ville`, `pays`, `zip_code`, `longitude`, `latitude`)"
        +"VALUES ('"+id_lieu+"', '"+nom_lieu+"', '"+rue_lieu+"', '"+ville_lieu+"', '"+pays_lieu+"', '"+zip_code_lieu+"', '"+longitude_lieu+"', '"+latitude_lieu+"');");
}

public void complete_tagger(Long id_geo,Long id_friend)throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`tagger` (`id_geo` ,`id_friend`)"
        +"VALUES ('"+id_geo+"', '"+id_friend+"');");     
}

//GROUPE
public void complete_groupe(long id_groupe, String nom_groupe) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`groupe` (`id_groupe` ,`nom`)"
        +"VALUES ('"+id_groupe+"', '"+nom_groupe+"');");
}
public void complete_appartient(long id_user, long id_groupe) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`appartient` (`id_user`, `id_groupe`) "
        + "VALUES ('"+id_user+"', '"+id_groupe+"'); ");
}

//EDUCATION
public void complete_education(long id_education, String nom_education) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`education` (`id_education`, `nomEcole`) "
        + "VALUES('"+id_education+"', '"+nom_education+"'); ");
}
public void complete_specialite(long id_specialite, String nom_specialite) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`specialite` (`id_specialite`, `nom_specialite`) "
        + "VALUES('"+id_specialite+"', '"+nom_specialite+"'); ");
}
public void complete_year(long id_year, int nom_year) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`year` (`id_year`, `nom_year`) "
        + "VALUES('"+id_year+"', '"+nom_year+"'); ");
}
public void complete_etudie(long id_user, long id_education, long id_specialite, long id_year_educ) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`etudie` (`id_user`, `id_education`, `id_specialite`, `id_year_educ`) "
        + "VALUES('"+id_user+"', '"+id_education+"', '"+id_specialite+"', '"+id_year_educ+"'); ");
}

//TRAVAIL
public void complete_travail(long id_travail, String nom_entreprise) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`travail` (`id_travail`, `nomEntreprise`) "
        + "VALUES('"+id_travail+"', '"+nom_entreprise+"'); ");
}
public void complete_travaille_dans(long id_user, long id_travail) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`travaille_dans` (`id_user`, `id_travail`) "
        + "VALUES('"+id_user+"', '"+id_travail+"'); ");
}

//LIKE
public void complete_like(long id_like, String nom_like, String categorie_like) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`like` (`id_like` ,`nom` ,`categorie`)"
        +"VALUES ('"+id_like+"', '"+nom_like+"', '"+categorie_like+"');");   
}
public void complete_aime(long id_user, long id_aime) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`aime` (`id_user`, `id_like`) "
        + "VALUES ('"+id_user+"', '"+id_aime+"'); ");
}

//EVENT
public void complete_event(long id_event, String nom_event) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`event` (`id_event` ,`nom`)"
        + "VALUES ('"+id_event+"', '"+nom_event+"'); ");
}
public void complete_participe(long id_user, long id_event) throws SQLException{
    myStatement.executeUpdate("INSERT INTO  `data_from_facebook`.`participe` (`id_user`, `id_event`) "
        + "VALUES ('"+id_user+"', '"+id_event+"'); ");
}

    
/*******************************
** **
** **
*********************************/
public boolean login(String oauth_token, String fb_id, String ipAddress) throws SQLException{
   // System.out.println("Fct login => ");
    Statement st;
    ResultSet result;


    //L'objet statement va permettre de manipuler les requetes sql
    myStatement = connectToBDD.createStatement();
    st = connectToBDD.createStatement();

    try {
        System.out.println("try login");
        System.out.println("\n\n *** PROFILE USER ***");
        String urlStr = "https://graph.facebook.com/me?access_token=" + oauth_token;
        String response = (sendGET(urlStr)).trim();
        System.out.println("url response: "+response);

        if(response != null) {

            /**********************************
            ***   INSERTION PROFILE USER    ***
            ***********************************/
            JSONObject profile = new JSONObject(response);
            if (profile.has("error")) {
                loginError = profile.getJSONObject("error").toString();
                return false;
            }

            //USER est-il deja dans la table?
            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`user` WHERE  `id_user` = '"+profile.getLong("id")+"'");
            if(result.next() == false){
                String lastnameAvecDblQuotes = (profile.getString("last_name")).replaceAll("'", " ");
                String firstnameAvecDblQuotes = (profile.getString("first_name")).replaceAll("'", " ");
                //Pas de LOCATION alors on met 0
                if(profile.isNull("location")){
                    System.out.println("PAS DE LOCATION");
                    //Pas de GENDER
                    if(profile.isNull("gender")){
                        System.out.println("PAS DE GENDER");   
                        //Pas de BIRTHDAY
                        if(profile.isNull("birthday")){
                            System.out.println(" 1 PAS DE BIRTHDAY");
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0","0",0,profile.getString("updated_time"));
                        }
                        //BIRTHDAY
                        else{
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0",profile.getString("birthday"),0,profile.getString("updated_time"));
                        }  
                    }
                    //GENDER
                    else{
                         //Pas de BIRTHDAY alors on met 0
                        if(profile.isNull("birthday")){
                            System.out.println(" 2 PAS DE BIRTHDAY");
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,profile.getString("gender"),"0",0,profile.getString("updated_time"));
                        }
                        //BIRTHDAY
                        else{
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,profile.getString("gender"),profile.getString("birthday"),0,profile.getString("updated_time"));
                        }   
                    }
                }
                //LOCATION
                else{
                    JSONObject locationUser = profile.getJSONObject("location");                    
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`location` WHERE  `id_location` = '"+locationUser.getLong("id")+"'");
                    if(result.next() == false){
                        complete_location(locationUser.getLong("id"), locationUser.getString("name"));
                    }
                    result.close();

                    //Pas GENDER
                    if(profile.isNull("gender")){
                        System.out.println("PAS DE GENDER");    
                        //Pas BIRTHDAY alors on met 0
                        if(profile.isNull("birthday")){
                            System.out.println(" 3 PAS DE BIRTHDAY");
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0","0",locationUser.getLong("id"),profile.getString("updated_time"));
                        }
                        //BIRTHDAY
                        else{
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0",profile.getString("birthday"),locationUser.getLong("id"),profile.getString("updated_time"));
                        }  
                    }
                    //GENDER
                    else{
                        //Pas de BIRTHDAY alors on met 0
                        if(profile.isNull("birthday")){
                            System.out.println(" 4 PAS DE BIRTHDAY");
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,profile.getString("gender"),"0",locationUser.getLong("id"),profile.getString("updated_time"));
                        }
                        //BIRTHDAY
                        else{
                            complete_user(profile.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,profile.getString("gender"),profile.getString("birthday"),locationUser.getLong("id"),profile.getString("updated_time"));
                        }     
                    }// FIN ELSE VERIF GENDER
                }// FIN ELSE VERIF LOCATION
            }// FIN IF VERIF USER ds TABLE
            result.close(); 
            
            /**********************************
            ***   INSERTION GROUPES USER    ***
            ***********************************/
            urlStr = "https://graph.facebook.com/me/groups?access_token=" + oauth_token;
            String repGroupUser = (sendGET(urlStr)).trim();
            if(repGroupUser != null){
                JSONArray groupes = new JSONObject(repGroupUser).getJSONArray("data");
                for(int i=0; i<groupes.length(); i++){
  
                    //Si l'URL affiche error au lieu des groupes
                    if(groupes.getJSONObject(i).has("error")){
                        System.out.println("PROBLEM avec URL GROUPE USER: \n"+
                                groupes.getJSONObject(i).getJSONObject("error").toString()+"\n");
                        return false;
                    }
                    
                    //AJOUT GROUPE dans BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`groupe` WHERE  `id_groupe` =  '"+groupes.getJSONObject(i).getLong("id")+"'");
                    if(result.next() == false){
                        String nameAvecDblQuotes = (groupes.getJSONObject(i).getString("name")).replaceAll("'", " ");
                        complete_groupe(groupes.getJSONObject(i).getLong("id"), nameAvecDblQuotes); 
                    }// FIN IF
                    result.close();
                    
                    //AJOUT APPARTIENT dans BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`appartient` WHERE  `id_user` = '"+profile.getLong("id")
                            +"' AND `id_groupe`= '"+groupes.getJSONObject(i).getLong("id")+"';");
                    if(result.next() == false){
                        complete_appartient(profile.getLong("id"),groupes.getJSONObject(i).getLong("id"));
                    }
                    result.close();
                }// FIN FOR GROUPE
            }
            else{
                return false;
            }

            /**********************************
            ***    INSERTION LIKES USER     ***
            ***********************************/
            urlStr = "https://graph.facebook.com/me/likes?access_token=" + oauth_token;
            String repLikesUser = (sendGET(urlStr)).trim();
            if(repLikesUser != null){
                JSONArray likes = new JSONObject(repLikesUser).getJSONArray("data");   
                for(int i=0; i<likes.length(); i++){ 
                    
                    //Si l'URL affiche error au lieu des likes
                    if(likes.getJSONObject(i).has("error")){
                        System.out.println("PROBLEM avec URL LIKES USER: \n"+likes.getJSONObject(i).getJSONObject("error").toString()+"\n");
                        return false;
                    }
                    
                    //AJOUT LIKE dans BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`like` WHERE  `id_like` =  '"+likes.getJSONObject(i).getLong("id")+"' ");
                    if(result.next() == false){
                        String nameAvecDblQuotes = (likes.getJSONObject(i).getString("name")).replaceAll("'", " ");
                        String categoryAvecDblQuotes = (likes.getJSONObject(i).getString("category")).replaceAll("'", " ");
                        complete_like(likes.getJSONObject(i).getLong("id"), nameAvecDblQuotes, categoryAvecDblQuotes); 
                    }
                    result.close();
                    
                    //AJOUT AIME dans BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`aime` WHERE  `id_user` = '"+profile.getLong("id")
                            +"' AND `id_like`= '"+likes.getJSONObject(i).getLong("id")+"';");
                    if(result.next() == false){
                        complete_aime(profile.getLong("id"),likes.getJSONObject(i).getLong("id"));
                    }
                    result.close();
                }// FIN FOR LIKE    
            }
            else{
                return false;
            }
                                      
            /**********************************
            ***    INSERTION EVENTS USER    ***
            ***********************************/
            urlStr = "https://graph.facebook.com/me/events?access_token=" + oauth_token;
            String repEventsUser = (sendGET(urlStr)).trim();
            if(repEventsUser != null){
                JSONArray events = new JSONObject(repEventsUser).getJSONArray("data");
                //Insertion dans la BDD de EVENT
                for(int i=0; i<events.length(); i++){
                    
                    //Si l'URL affiche error au lieu des events
                    if(events.getJSONObject(i).has("error")){
                        System.out.println("PROBLEM avec URL EVENTS USER: \n"+events.getJSONObject(i).getJSONObject("error").toString()+"\n");
                        return false;
                    }
                    
                    //AJOUT EVENT dans BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`event` WHERE  `id_event` =  '"+events.getJSONObject(i).getLong("id")+"' ");
                    if(result.next() == false){
                        String nameAvecDblQuotes = (events.getJSONObject(i).getString("name")).replaceAll("'", " ");
                        complete_event(events.getJSONObject(i).getLong("id"), nameAvecDblQuotes);
                    }
                    result.close();
                    
                    //AJOUT PARTICIPE dans BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`participe` WHERE  `id_user` = '"+profile.getLong("id")
                            +"' AND `id_event`= '"+events.getJSONObject(i).getLong("id")+"';");
                    if(result.next() == false){
                        complete_participe(profile.getLong("id"),events.getJSONObject(i).getLong("id"));
                    }
                    result.close();
                }// FIN FOR EVENT
            }
            else{
                return false;
            }
            
            /**********************************
            ***    INSERTION EDUCATION USER ***
            ***********************************/
            if(profile.isNull("education")){
                System.out.println("PAS D'EDUCATION pour le MAIN USER");
            }
            //EDUCATION
            else{
                //JSONArray educ = new JSONObject(response).getJSONArray("education");
                JSONArray educs = profile.getJSONArray("education");
                System.out.println("Education: "+ educs);
                for(int i=0; i<educs.length(); i++){
                    JSONObject educ = educs.getJSONObject(i);
                    JSONObject school = educ.getJSONObject("school");
                    
                    //Ajout EDUCATION dans la BDD
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`education` WHERE  `id_education` =  '"+school.getLong("id")+"'");
                    if(result.next() == false){
                        String nameAvecDblQuotes = (school.getString("name")).replaceAll("'", " ");
                        complete_education(school.getLong("id"),nameAvecDblQuotes);
                    }
                    result.close();

                    //Pas YEAR alors on met vide
                    if(educ.isNull("year")){
                        System.out.println("  PAS DE YEAR MAIN USER");
                        //Pas CONCENTRATION alors on met vide
                        if(educ.isNull("concentration")){
                            System.out.println("  PAS DE CONCENTRATION pour EDUCATION le MAIN USER");
                            //AJOUT ETUDIE dans BDD
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                + "`id_user` = '"+profile.getLong("id")
                                +"' AND `id_education`= '"+school.getLong("id")
                                +"' AND `id_specialite`= '"+0
                                +"' AND `id_year_educ`= '"+0+"';");
                            if(result.next() == false){
                               complete_etudie(profile.getLong("id"),school.getLong("id"),0,0);
                            }
                            result.close(); 
                        }
                        // CONCENTRATION
                        else{
                            JSONArray specs = educ.getJSONArray("concentration");
                            JSONObject spec = specs.getJSONObject(0);
                            System.out.println("spec 1 =>"+ spec);
                            /*** /!\ (a voir plus tard) Si on veut récupérer toutes les educations
                            for(int j=0; j<spec.length(); j++){
                                JSONObject specialite = spec.getJSONObject(j);
                                System.out.println("spe "+i+": "+specialite);
                            }(ne pas enlever)***/

                             //AJOUT SPECIALITE dans BDD
                            String specAvecDblQuotes = (spec.getString("name")).replaceAll("'", " ");
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`specialite` WHERE  `id_specialite` =  '"+spec.getLong("id")+"'");
                            if(result.next() == false){
                                complete_specialite(spec.getLong("id"),specAvecDblQuotes);
                            }
                            result.close();
                            //AJOUT ETUDIE dans BDD
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                + "`id_user` = '"+profile.getLong("id")
                                +"' AND `id_education`= '"+school.getLong("id")
                                +"' AND `id_specialite`= '"+spec.getLong("id")
                                +"' AND `id_year_educ`= '"+0+"';"); 
                            if(result.next() == false){
                               complete_etudie(profile.getLong("id"),school.getLong("id"),spec.getLong("id"),0);
                            }
                            result.close();
                        }// FIN ELSE VERIF CONCENTRATION    
                    }// YEAR
                    else{
                        JSONObject yearSchool = educ.getJSONObject("year");  
                        //AJOUT YEAR dans BDD
                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`year` WHERE  `id_year` =  '"+yearSchool.getLong("id")+"'");
                        if(result.next() == false){
                            complete_year(yearSchool.getLong("id"), Integer.parseInt(yearSchool.getString("name")));
                        }
                        result.close();

                        //Pas CONCENTRATION
                        if(educ.isNull("concentration")){
                            //AJOUT ETUDIE dans BDD
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                + "`id_user` = '"+profile.getLong("id")
                                +"' AND `id_education`= '"+school.getLong("id")
                                +"' AND `id_specialite`= '"+0
                                +"' AND `id_year_educ`= '"+yearSchool.getLong("id") +"';"); 
                            if(result.next() == false){
                               complete_etudie(profile.getLong("id"),school.getLong("id"),0,yearSchool.getLong("id"));
                            }
                            result.close();
                        }
                        // CONCENTRATION
                        else{
                            JSONArray specs = educ.getJSONArray("concentration");
                            JSONObject spec = specs.getJSONObject(0);
                            //AJOUT SPECIALITE dans BDD
                            String specAvecDblQuotes = (spec.getString("name")).replaceAll("'", " ");
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`specialite` WHERE  `id_specialite` =  '"+spec.getLong("id")+"'");
                            if(result.next() == false){
                                complete_specialite(spec.getLong("id"), specAvecDblQuotes);
                            }
                            result.close();

                            //AJOUT ETUDIE dans BDD
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                + "`id_user` = '"+profile.getLong("id")
                                +"' AND `id_education`= '"+school.getLong("id")
                                +"' AND `id_specialite`= '"+spec.getLong("id")
                                +"' AND `id_year_educ`= '"+yearSchool.getLong("id")+"';"); 
                            if(result.next() == false){
                               complete_etudie(profile.getLong("id"),school.getLong("id"),spec.getLong("id"),yearSchool.getLong("id"));
                            }
                            result.close();
                        }// FIN ELSE VERIF CONCENTRATION                  
                    }// FIN ELSE VERIF YEAR
                } // FIN FOR    
            } // FIN ELSE EDUC

            /**********************************
            ***    INSERTION WORKS USER     ***
            ***********************************/
            if(profile.isNull("work")){
                System.out.println("PAS DE WORK pour le MAIN USER");
            }
            //WORK
            else{
                JSONArray work = profile.getJSONArray("work");
                System.out.println("Entreprise "+ work);
                //Education contient plusieurs ecoles
                for(int i=0; i<work.length(); i++){
                    JSONObject entreprise = work.getJSONObject(i);
                    JSONObject nameEntreprise = entreprise.getJSONObject("employer");

                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`travail` WHERE  `id_travail` =  '"+nameEntreprise.getLong("id")+"'");
                    if(result.next() == false){
                        String nameAvecDblQuotes = (nameEntreprise.getString("name")).replaceAll("'", " ");
                        complete_travail(nameEntreprise.getLong("id"),nameAvecDblQuotes);
                    }
                    result.close();
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`travaille_dans` WHERE  `id_user` = '"+profile.getLong("id")
                        +"' AND `id_travail`= '"+nameEntreprise.getString("id")+"';");
                    if(result.next() == false){
                       complete_travaille_dans(profile.getLong("id"),nameEntreprise.getLong("id"));
                    }
                    result.close();                       
                } // FIN FOR WORK   
              }// FIN ELSE VERIF WORK


            /**********************************
            ***    INSERTION AMIS USER      ***
            ***********************************/
            System.out.println("\n\n *** FRIENDS LIST ***");
            urlStr = "https://graph.facebook.com/me/friends?access_token=" + oauth_token;
            String rep = (sendGET(urlStr)).trim();
            if(rep != null){
                JSONArray friends = new JSONObject(rep).getJSONArray("data");
                 
                for (int i=0; i<friends.length(); i++){
                    JSONObject friend = friends.getJSONObject(i);
                    System.out.println("\nFRIEND "+i+":" +friend);
                    //Si l'URL affiche error au lieu de l'amis
                    if(friend.has("error")){
                        System.out.println("PROBLEM avec URL FRIEND de USER: \n"+
                                friend.getJSONObject("error").toString()+"\n");
                        return false;
                    }
                   
                    //URL menant au profil de l'ami
                    urlStr = "https://graph.facebook.com/"+ friend.getLong("id") +"?access_token=" + oauth_token;
                    String resp2 = sendGET(urlStr);
                    while(resp2.equals("false")){
                        resp2 = sendGET(urlStr);
                    }
                    System.out.println("URL INFOFRIEND =>>> "+urlStr);
                    System.out.println("SENDGET URL INFOFRIEND =>>> "+sendGET(urlStr));
                    if(resp2 != null){
                        JSONObject infoFriend = new JSONObject(resp2);
                                               
                        //Si l'URL affiche error au lieu des infos d'amis
                        if(infoFriend.has("error")){
                            System.out.println("PROBLEM avec URL FRIEND de USER: \n"+
                                    infoFriend.getJSONObject("error").toString()+"\n");
                            return false;
                        }
                        
                        // URL Menant au GROUPES des AMIS de USER
                        urlStr = "https://graph.facebook.com/"+ friend.getLong("id") +"/groups?access_token=" + oauth_token;
                        String repGroupes = (sendGET(urlStr)).trim();
                        JSONArray groupesFriend = new JSONObject(repGroupes).getJSONArray("data");

                        // URL Menant au LIKES des AMIS de USER
                        urlStr = "https://graph.facebook.com/"+ friend.getLong("id") +"/likes?access_token=" + oauth_token;
                        String repLikes = (sendGET(urlStr)).trim();
                        JSONArray likesFriend = new JSONObject(repLikes).getJSONArray("data");

                         // URL Menant au EVENTS des AMIS de USER
                        urlStr = "https://graph.facebook.com/"+ friend.getLong("id") +"/events?access_token=" + oauth_token;
                        String repEvents = (sendGET(urlStr)).trim();
                        JSONArray eventsFriend = new JSONObject(repEvents).getJSONArray("data"); 

                        System.out.println("INFO FRIEND:" + infoFriend);

                         
                        /**********************************
                        ***   INSERTION PROFILE FRIENDS ***
                        ***********************************/ 
                        //AJOUT USER (Friend) dans BDD
                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`user` WHERE  `id_user` = '"+infoFriend.getString("id")+"'");
                        if(result.next() == false){
                            String lastnameAvecDblQuotes = (infoFriend.getString("last_name")).replaceAll("'", " ");
                            String firstnameAvecDblQuotes = (infoFriend.getString("first_name")).replaceAll("'", " ");
                            //Pas LOCATION 
                            if(infoFriend.isNull("location")){
                                System.out.println("PAS LOCATION");
                                //Pas GENDER 
                                if(infoFriend.isNull("gender")){
                                    System.out.println("PAS GENDER 1"); 
                                    //Pas BIRTHDAY 
                                    if(infoFriend.isNull("birthday")){
                                        System.out.println("PAS BIRTHDAY 1");
                                        complete_user(infoFriend.getLong("id"), lastnameAvecDblQuotes, firstnameAvecDblQuotes, "0", "0", 0,infoFriend.getString("updated_time")); 
                                    }
                                    //BIRTHDAY
                                    else{
                                        System.out.println("BIRTHDAY 1");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0",infoFriend.getString("birthday"),0,infoFriend.getString("updated_time"));
                                    }  
                                }
                                //GENDER
                                else {
                                    System.out.println("GENDER 1");
                                    //Pas BIRTHDAY 
                                    if(infoFriend.isNull("birthday")){
                                        System.out.println("PAS BIRTHDAY 2");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,infoFriend.getString("gender"),"0",0,infoFriend.getString("updated_time")); 
                                    }
                                    //BIRTHDAY
                                    else{
                                        System.out.println("BIRTHDAY 2");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,infoFriend.getString("gender"),infoFriend.getString("birthday"),0,infoFriend.getString("updated_time"));
                                    }     
                                }                        
                            }
                            //LOCATION
                            else{
                                System.out.println("LOCATION");
                                JSONObject locationFriendUser = infoFriend.getJSONObject("location");                        
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`location` WHERE  `id_location` =  '"+locationFriendUser.getLong("id")+"'");
                                if(result.next() == false){
                                    complete_location(locationFriendUser.getLong("id"), locationFriendUser.getString("name"));
                                }
                                result.close();
                                //Pas GENDER
                                if(infoFriend.isNull("gender")){
                                    System.out.println("PAS GENDER 2");
                                    //Pas BIRTHDAY 
                                    if(infoFriend.isNull("birthday")){
                                        System.out.println("PAS BIRTHDAY 3");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0","0",locationFriendUser.getLong("id"),infoFriend.getString("updated_time"));                                        
                                    }
                                    //BIRTHDAY
                                    else{
                                        System.out.println("BIRTHDAY 3");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,"0",infoFriend.getString("birthday"),locationFriendUser.getLong("id"),infoFriend.getString("updated_time"));
                                    }  
                                }
                                //GENDER
                                else{
                                    System.out.println("GENDER 2");
                                    //Pas de BIRTHDAY alors on met 0
                                    if(infoFriend.isNull("birthday")){
                                        System.out.println("PAS BIRTHDAY 4");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,infoFriend.getString("gender"),"0",locationFriendUser.getLong("id"),infoFriend.getString("updated_time"));
                                    }
                                    //BIRTHDAY
                                    else{
                                        System.out.println("BIRTHDAY 4");
                                        complete_user(infoFriend.getLong("id"),lastnameAvecDblQuotes,firstnameAvecDblQuotes,infoFriend.getString("gender"),infoFriend.getString("birthday"),locationFriendUser.getLong("id"),infoFriend.getString("updated_time")); 
                                    }                               
                                }
                            }
                        } // FIN IF FRIEND DEJA ds TABLE
                        result.close(); 

                        /**********************************
                        ***   INSERTION GROUPES FRIENDS ***
                        ***********************************/ 
                        if(repGroupes != null){ 
                            for(int n=0; n<groupesFriend.length(); n++){ 
                                JSONObject grFriend = groupesFriend.getJSONObject(n);
                                //Si l'URL affiche error au lieu de l'amis
                                if(grFriend.has("error")){
                                    System.out.println("PROBLEM avec URL GROUPE FRIEND: \n"+grFriend.getJSONObject("error").toString()+"\n");
                                    return false;
                                }
                                //AJOUT GROUPE dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`groupe` WHERE  `id_groupe` =  '"+grFriend.getLong("id")+"'");
                                if(result.next() == false){
                                    String nameAvecDblQuotes = (grFriend.getString("name")).replaceAll("'", " ");
                                    complete_groupe(grFriend.getLong("id"),nameAvecDblQuotes);                      
                                }// FIN IF
                                result.close();
                                
                                //AJOUT APPARTIENT dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`appartient` WHERE  `id_user` = '"+infoFriend.getLong("id")
                                    +"' AND `id_groupe`= '"+grFriend.getLong("id")+"';");
                                if(result.next() == false){
                                    complete_appartient(infoFriend.getLong("id"),grFriend.getLong("id"));
                                }
                                result.close();
                            }// FIN FOR GROUPE FRIEND 
                        }else{
                            return false;
                        }

                        /**********************************
                        ***   INSERTION LIKES FRIENDS   ***
                        ***********************************/ 
                        if(repLikes != null){
                            for(int h=0; h<likesFriend.length(); h++){  
                                JSONObject lkFriend = likesFriend.getJSONObject(h);
                                //Si l'URL affiche error au lieu des likes
                                if(lkFriend.has("error")){
                                    System.out.println("PROBLEM avec URL LIKE FRIEND: \n"+
                                            lkFriend.getJSONObject("error").toString()+"\n");
                                    return false;
                                }
                                
                                //AJOUT LIKE dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`like` WHERE  `id_like` =  '"+lkFriend.getLong("id")+"'");
                                if(result.next() == false){
                                    String nameAvecDblQuotes = (lkFriend.getString("name")).replaceAll("'", " ");
                                    String categoryAvecDblQuotes = (lkFriend.getString("category")).replaceAll("'", " ");
                                    complete_like(lkFriend.getLong("id"),nameAvecDblQuotes,categoryAvecDblQuotes);                      
                                }// FIN IF
                                result.close();
                                
                                //AJOUT AIME dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`aime` WHERE  `id_user` = '"+infoFriend.getLong("id")
                                    +"' AND `id_like`= '"+lkFriend.getLong("id")+"';");
                                if(result.next() == false){
                                    complete_aime(infoFriend.getLong("id"),lkFriend.getLong("id"));
                                }
                                result.close();
                            }// FIN FOR LIKE FRIEND
                        }
                        else{
                            return false;
                        }

                        /**********************************
                        *** INSERTION EVENTS FRIENDS    ***
                        ***********************************/
                        if(repEvents != null){
                            for(int j=0; j<eventsFriend.length(); j++){
                                
                                JSONObject evtFriend = eventsFriend.getJSONObject(j);
                                //Si l'URL affiche error au lieu des events
                                if(evtFriend.has("error")){
                                    System.out.println("PROBLEM avec URL EVENT FRIEND: \n"+
                                            evtFriend.getJSONObject("error").toString()+"\n");
                                    return false;
                                }
                                
                               //AJOUT EVENT dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`event` WHERE  `id_event` =  '"+evtFriend.getLong("id")+"' ");
                                if(result.next() == false){
                                    String nameAvecDblQuotes = (evtFriend.getString("name")).replaceAll("'", " ");
                                    complete_event(evtFriend.getLong("id"),nameAvecDblQuotes);                   
                                }
                                result.close();
                                
                                //AJOUT PARTICIPE dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`participe` WHERE  `id_user` = '"+infoFriend.getLong("id")
                                        +"' AND `id_event`= '"+evtFriend.getString("id")+"';");
                                if(result.next() == false){
                                    complete_participe(infoFriend.getLong("id"),evtFriend.getLong("id"));
                                }
                                result.close();
                            }
                        }
                        else{
                            return false;
                        }

                        /**********************************
                        *** INSERTION EDUCATION FRIENDS ***
                        ***********************************/
                        //Pas EDUCATION alors on n'en ajoute pas
                        if(infoFriend.isNull("education")){
                            System.out.println("PAS EDUCATION");
                        }
                        //EDUCATION
                        else{
                            JSONArray educs = infoFriend.getJSONArray("education");
                            System.out.println("EDUCATION "+ educs);
                            //Education contient plusieurs ecoles
                            for(int j=0; j<educs.length(); j++){
                                JSONObject educ = educs.getJSONObject(j);
                                System.out.println("   EDUC "+j+": "+educ);
                                JSONObject school = educ.getJSONObject("school");

                                //Ajout EDUCATION dans la BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`education` WHERE  `id_education` =  '"+school.getLong("id")+"'");
                                if(result.next() == false){
                                    String nameAvecDblQuotes = (school.getString("name")).replaceAll("'", " ");
                                    complete_education(school.getLong("id"),nameAvecDblQuotes);
                                }
                                result.close();

                                //Pas YEAR
                                if(educ.isNull("year")){
                                    System.out.println("   PAS YEAR");
                                    //Pas CONCENTRATION
                                    if(educ.isNull("concentration")){
                                        System.out.println("   PAS CONCENTRATION");
                                        //AJOUT ETUDIE dans BDD
                                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                            + "`id_user` = '"+infoFriend.getLong("id")
                                            +"' AND `id_education`= '"+school.getLong("id")
                                            +"' AND `id_specialite`= '"+0
                                            +"' AND `id_year_educ`= '"+0+"';");
                                        if(result.next() == false){
                                           complete_etudie(infoFriend.getLong("id"),school.getLong("id"),0,0);
                                        }
                                        result.close(); 
                                    }
                                    // CONCENTRATION
                                    else{
                                        JSONArray specs = educ.getJSONArray("concentration");
                                        System.out.println("   CONCENTRATION "+specs);
                                        JSONObject spec = specs.getJSONObject(0);

                                        //AJOUT SPECIALITE dans BDD
                                        String specAvecDblQuotes = (spec.getString("name")).replaceAll("'", " ");
                                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`specialite` WHERE  `id_specialite` =  '"+spec.getLong("id")+"'");
                                        if(result.next() == false){
                                            complete_specialite(spec.getLong("id"),specAvecDblQuotes);
                                        }
                                        result.close();
                                        //AJOUT ETUDIE dans BDD
                                        //result = verif_etudie(Long.parseLong(infoFriend.getString("id")),Long.parseLong(school.getString("id")),Long.parseLong(spec.getString("id")),0);
                                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                            + "`id_user` = '"+infoFriend.getLong("id")
                                            +"' AND `id_education`= '"+school.getLong("id")
                                            +"' AND `id_specialite`= '"+spec.getLong("id")
                                            +"' AND `id_year_educ`= '"+0+"';");
                                        if(result.next() == false){
                                           complete_etudie(infoFriend.getLong("id"),school.getLong("id"),spec.getLong("id"),0);
                                        }
                                        result.close();
                                    }// FIN ELSE VERIF CONCENTRATION    
                                }// FIN IF VERIF YEAR
                                // YEAR
                                else{
                                    JSONObject yearSchool = educ.getJSONObject("year");
                                    System.out.println("   YEAR: " +yearSchool);
                                    
                                    //AJOUT YEAR dans BDD
                                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`year` WHERE  `id_year` =  '"+yearSchool.getLong("id")+"'");
                                    if(result.next() == false){
                                        complete_year(yearSchool.getLong("id"),Integer.parseInt(yearSchool.getString("name")));
                                    }
                                    result.close();

                                    //Pas CONCENTRATION
                                    if(educ.isNull("concentration")){
                                        System.out.println("   PAS CONCENTRATION");
                                        //AJOUT ETUDIE dans BDD
                                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                            + "`id_user` = '"+infoFriend.getLong("id")
                                            +"' AND `id_education`= '"+school.getLong("id")
                                            +"' AND `id_specialite`= '"+0
                                            +"' AND `id_year_educ`= '"+yearSchool.getLong("id")+"';");
                                        if(result.next() == false){
                                           complete_etudie(infoFriend.getLong("id"),school.getLong("id"),0,yearSchool.getLong("id"));
                                        }
                                        result.close();
                                    }
                                    // CONCENTRATION
                                    else{
                                        JSONArray specs = educ.getJSONArray("concentration");
                                        System.out.println("   CONCENTRATION: "+specs);
                                        JSONObject spec = specs.getJSONObject(0);

                                        //AJOUT SPECIALITE dans BDD
                                        String specAvecDblQuotes = (spec.getString("name")).replaceAll("'", " ");
                                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`specialite` WHERE  `id_specialite` =  '"+spec.getLong("id")+"'");
                                        if(result.next() == false){
                                            complete_specialite(spec.getLong("id"),specAvecDblQuotes);
                                        }
                                        result.close();
                                        
                                        //AJOUT ETUDIE dans BDD
                                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`etudie` WHERE "
                                            + "`id_user` = '"+infoFriend.getLong("id")
                                            +"' AND `id_education`= '"+school.getLong("id")
                                            +"' AND `id_specialite`= '"+spec.getLong("id")
                                            +"' AND `id_year_educ`= '"+yearSchool.getLong("id")+"';");
                                        if(result.next() == false){
                                           complete_etudie(infoFriend.getLong("id"),school.getLong("id"),spec.getLong("id"),yearSchool.getLong("id"));
                                        }
                                        result.close();
                                   }// FIN ELSE VERIF CONCENTRATION                  
                                }// FIN ELSE VERIF YEAR
                            } // FIN FOR
                        } // FIN ELSE EDUC 

                        /**********************************
                        ***   INSERTION WORK FRIENDS    ***
                        ***********************************/                          
                        //Pas WORK alors on n'en ajoute pas
                        if(infoFriend.isNull("work")){
                            System.out.println("PAS DE WORK");
                        }
                        //WORK
                        else{ 
                            JSONArray works = infoFriend.getJSONArray("work");

                            System.out.println("Entreprise "+ works);
                            //Education contient plusieurs ecoles
                            for(int j=0; j<works.length(); j++){
                                JSONObject entreprise = works.getJSONObject(j);
                                // System.out.println("    Work "+i+": "+entreprise);
                                JSONObject nameEntreprise = entreprise.getJSONObject("employer");
                                
                                //AJOUT TRAVAIL dans BDD                                   
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`travail` WHERE  `id_travail` =  '"+nameEntreprise.getLong("id")+"'");
                                if(result.next() == false){
                                        String nameAvecDblQuotes = (nameEntreprise.getString("name")).replaceAll("'", " ");                   
                                        complete_travail(nameEntreprise.getLong("id"),nameAvecDblQuotes);
                                }
                                result.close();
                                
                                //AJOUT TRAVAILLE_DANS dans BDD
                                result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`travaille_dans` WHERE  `id_user` = '"+infoFriend.getLong("id")
                                        +"' AND `id_travail`= '"+nameEntreprise.getLong("id")+"';");
                                if(result.next() == false){
                                   complete_travaille_dans(infoFriend.getLong("id"),nameEntreprise.getLong("id"));
                                }
                                result.close(); 
                            } // FIN FOR WORK   
                        }// FIN ELSE VERIF WORK 
                        
                        /**********************************
                        *** INSERTION AMITIE ***
                        ***********************************/
                        result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`est_ami` WHERE  `id_user` = '"+profile.getLong("id")
                            +"' AND `id_friend`= '"+infoFriend.getString("id")+"';");
                        if(result.next() == false){
                            complete_est_ami(profile.getLong("id"),infoFriend.getLong("id"));
                        }
                        result.close();
                    }
                    // FIN IF REP 2
                    else{
                        return false;
                    }
                }// FIN FOR FRIEND 
            }
            // FIN IF REP
            else{
                return false;
            }
            //FIN ELSE REP

            /******************************************
            ***   INSERTION GEO-LOCALISATION USER    ***
            *******************************************/
            urlStr = "https://graph.facebook.com/me/locations?access_token=" + oauth_token;
            String repGeoLocalisationUser = (sendGET(urlStr)).trim();
            System.out.println("GeoLocalisation : " + repGeoLocalisationUser);
            if(repGeoLocalisationUser != null){
                JSONArray geolocals = new JSONObject(repGeoLocalisationUser).getJSONArray("data");
                for(int i=0; i<geolocals.length(); i++){
                    //Si l'URL affiche error au lieu des geolocalisations
                    if(geolocals.getJSONObject(i).has("error")){
                        System.out.println("PROBLEM avec URL GEOLOCALISATION USER: \n"+
                        geolocals.getJSONObject(i).getJSONObject("error").toString()+"\n");
                        return false;
                    }
                    
                    //AJOUT LIEU dans BDD
                    JSONObject lieu = geolocals.getJSONObject(i).getJSONObject("place");
                    JSONObject location = lieu.getJSONObject("location");
                    System.out.println("LIEU : " + lieu.getLong("id")+" "+lieu.getString("name")+" "+location.getString("street")+" "+location.getString("city")+" "+location.getString("country")+" "+location.getString("zip")+" "+location.getDouble("longitude")+" "+location.getDouble("latitude")); 
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`lieu` WHERE  `id_lieu` =  '"+lieu.getLong("id")+"'");
                    if(result.next() == false){
                        complete_lieu(lieu.getLong("id"),lieu.getString("name"),location.getString("street"),location.getString("city"),location.getString("country"),location.getString("zip"),location.getDouble("longitude"),location.getDouble("latitude")); 
                    }// FIN IF
                    result.close();

                    //AJOUT GEOLOCALISATION dans BDD                                                                          
                    long iduser = geolocals.getJSONObject(i).getJSONObject("from").getLong("id"); 
                    result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`geolocalisation` WHERE  `id_geo` =  '"+geolocals.getJSONObject(i).getLong("id")+"'");
                    if(result.next() == false){
                        complete_geolocalisation(geolocals.getJSONObject(i).getLong("id"),geolocals.getJSONObject(i).getString("created_time"),lieu.getLong("id"),iduser); 
                    }// FIN IF
                    result.close();
                 
                    //AJOUT TAGGER dans BDD
                    if(geolocals.getJSONObject(i).isNull("tags")){
                        System.out.println("PAS DE TAGS");
                    }
                    else{
                        JSONArray tags = geolocals.getJSONObject(i).getJSONObject("tags").getJSONArray("data");
                        System.out.println("TAGS : " + tags);
                        for(int j=0;j<tags.length();j++){
                            JSONObject tag = tags.getJSONObject(j);
                            result = st.executeQuery("SELECT * FROM  `data_from_facebook`.`tagger` WHERE  `id_geo` = '"+geolocals.getJSONObject(i).getLong("id")
                                    +"' AND `id_friend`= '"+tag.getLong("id")+"';");
                            if(result.next() == false){
                                complete_tagger(geolocals.getJSONObject(i).getLong("id"),tag.getLong("id"));
                            }
                            result.close();
                        }
                    }
                }
            }//FIN GEOLOCALISATION
                                    
        }
        //FIN IF RESPONSE
        else {
            return false;
        }
    //FIN TRY
    } 
    catch (JSONException e) {
        e.printStackTrace();
    }
   // st.close();
    
    return true;
}

    /*******************************
** 	**
** 	**
*********************************/
public String streamToStr(InputStream in) throws IOException {
    //System.out.println("Fct streamToStr => ");
    StringBuffer out = new StringBuffer();
    byte[] b = new byte[4096];
    for (int n; (n = in.read(b)) != -1;) {
        out.append(new String(b, 0, n));
    }
    return out.toString();
}

/*******************************
** 	**
** 	**
*********************************/
public InputStream sendGETStream(String urlStr) {
    //System.out.println("Fct sendGETStream => ");
    try {
        URL url = new URL(urlStr);
        HttpURLConnection urlConnection = (HttpURLConnection) url.openConnection();
        urlConnection.setRequestMethod("GET");
        urlConnection.connect();
        InputStream result = urlConnection.getInputStream();
        //urlConnection.disconnect();
        return result;
    } catch (Exception e) {
        //logger.println(e.getMessage());
        //e.printStackTrace();
        return null;
    }
}

/*******************************
** 	**
** 	**
*********************************/
public String sendGET(String urlStr) {
    //System.out.println("Fct sendGET => ");      
    try{
        InputStream stream = sendGETStream(urlStr);
        return streamToStr(stream);
    } catch (Exception e) {
        //logger.println(e.getMessage());
       // e.printStackTrace();
        return null;
    }
}
%>



<%@page language="java" contentType="text/html" pageEncoding="UTF-8"%>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN"
   "http://www.w3.org/TR/html4/loose.dtd">
<html xmlns="http://www.w3.org/1999/xhtml" xmlns:fb='http://www.facebook.com/2008/fbml' lang="en" xml:lang="en">
    <head>
        <title>TP</title>
        <meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
        <meta name="viewport" content="initial-scale=1.0, user-scalable=no" />
        <style type="text/css">
            html { height: 100% }
            body { height: 100%; margin: 0px; padding: 0px } 
        </style>
        <script src="http://connect.facebook.net/en_US/all.js"></script>
    </head>
    <body > 
    <div align="justify">
        <div height="50px"> <center><h1> &nbsp;&nbsp; Bienvenue sur Insa&iuml;ens Because You Worth It! </h1></center></div>
        <div height="800px">
            <h2>&nbsp;&nbsp;
            *~ Cl&eacute;mence Ramondou ~*<br/><br/>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            *~ Nargisse Marine ~*<br/><br/>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            *~ Arnaud Fradin ~*<br/><br/>
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;
            *~ Morgan Sanichanh ~*</h2>
            <center><h2>vous remercient pour l'attention accord&eacute;e &agrave; notre projet tuteur&eacute;</h2></center> 
            <p>
            &nbsp;&nbsp;&nbsp;&nbsp;Dans le cadre de notre projet tuteur&eacute; de 4A IR I "Inf&eacute;rence sur les r&eacute;seaux sociaux",&nbsp;
            nous sommes amen&eacute;s &agrave; utiliser le r&eacute;seau social Facebook afin de r&eacute;cup&eacute;rer des informations publiques pour d&eacute;duire quel est votre d&eacute;partement d'&eacute;tude au sein de l'INSA de Toulouse.
            </p>
            <h3>Dans quel but?</h3>
            <p> &nbsp;&nbsp;&nbsp;&nbsp;Celui de d&eacute;noncer la non confidentialit&eacute; des r&eacute;seaux sociaux et, de ce fait,&nbsp;
            vous amener &agrave; &ecirc;tre vigilant lorsque vous publiez des informations dans les r&eacute;seaux sociaux auquels vous appartenez. <br/>
            </p>
            <h3> Mais qu'est-ce qu'une inf&eacute;rence?</h3>
            <p>&nbsp;&nbsp;&nbsp;&nbsp;Une inf&eacute;rence est un <i>"raisonnement d&eacute;ductif, tirant ses conclusions d'une proposition admise vraie"</i>.<br/>
            En clair : les "propositions vraies" sont vos donn&eacute;es mises sur Facebook et la "conclusion" sera de d&eacute;duire votre d&eacute;partement d'&eacute;tude.<br/>
            Actuellement, nous d&eacute;veloppons un algorithme d'apprentissage qui jouera le r&ocirc;le du "raisonnement d&eacute;ductif".</p> 
            <h3> En quoi montrez-vous la "non confidentialit&eacute; des r&eacute;seaux sociaux" en &eacute;tant capable de dire &agrave; "quel d&eacute;partement d'&eacute;tude j'appartiens"
                alors que cette information n'est pas confidentielle?<br/>
                <i>old version(alors que je peux moi-m&ecirc;me partager cette information dans mes r&eacute;seaux sociaux?)</i></h3>
            <p> &nbsp;&nbsp;&nbsp;&nbsp;Si nous avons choisi quelque chose de "simple" a d&eacute;terminer, c'est, tout simplement, parce que nous avons besoin de tester/valider notre inf&eacute;rence.<br/>
            Avec l'annuaire de l'INSA de Toulouse nous serons capable de valider notre inf&eacute;rence en comparant nos r&eacute;sultats trouv&eacute; avec l'annuaire.<br/></p>
            <h3>Seriez vous capable de d&eacute;finir des informations r&eacute;ellement priv&eacute;es sur moi, &agrave; partir des informations publiques mises sur facebook?</h3>
            <p>Oui, nous pourrions d&eacute;duire des choses bien plus priv&eacute;es.<br/>
            Voici deux exemples de ce qui a d&eacute;j&agrave; &eacute;t&eacute; d&eacute;nonc&eacute; :<br/>
            =>&nbsp;&nbsp;<a href="http://www.boston.com/bostonglobe/ideas/articles/2009/09/20/project_gaydar_an_mit_experiment_raises_new_questions_about_online_privacy/" target="_blank" style="text-decoration:none;">Un projet effectu&eacute; par le MIT.</a><br/>
            =>&nbsp;&nbsp;<a href="http://pleaserobme.com/" target="_blank" style="text-decoration:none;">Un autre projet "Please Robe Me" qui se sert de TWITTER pour d&eacute;noncer la non confidentialit&eacute; des r&eacute;seaux sociaux. </a> <br />
            </p>
            <p> Gr&acirc;ce &agrave; vous, nous allons pouvoir tester la deuxième partie de notre projet: l'algorithme d'apprentissage (l'inf&eacute;rence).<br /> 
                Nous vous en remercions.<br /></p>
        </div>
        <div height="50px"><center><small>Pour plus d'information, vous pouvez nous contacter => <a style="text-decoration:none;" href="mailto:ptutinsa@gmail.com" >ici</a></small></center></div>
    </div>
    </body>
</html>
